package com.qctpos.crm.webapi.controller;

import com.qctpos.crm.common.annotation.PassToken;
import com.qctpos.crm.common.annotation.ResponseResult;
import com.qctpos.crm.common.utils.Base64Util;
import com.qctpos.crm.common.utils.DESUtils;
import com.qctpos.crm.common.utils.ExceptionUtil;
import com.qctpos.crm.domain.dto.ExSqlDto;
import com.qctpos.crm.domain.dto.UserLoginDto;
import com.qctpos.crm.domain.dto.UserLoginResultDto;
import com.qctpos.crm.service.Demo1Service;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import lombok.var;
import org.springframework.amqp.rabbit.core.RabbitTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.UUID;

@Controller
@Api(tags = "Sql管理")
@PassToken
@Slf4j
public class SqlController {

    @Autowired
    private Demo1Service baseService;

    /**
     *
     * @return
     */
    @GetMapping(value ={"/loginsql"})
    @ApiOperation(value = "sql", notes = "sql")
    public String Loginsql(){

        return "login";

    }


    @PostMapping("/loginsql")
    public String main(UserLoginDto user, HttpSession session, Model model){ //RedirectAttributes

        if("qctdb".equals(user.getUserAccount()) && "qctdb123".equals(user.getPassword())){
            //把登陆成功的用户保存起来
            session.setAttribute("dbLoginsqlUser",user);
            session.setMaxInactiveInterval(1800);
            //登录成功重定向到main.html;  重定向防止表单重复提交
            UUID uuid = UUID.randomUUID();
            return "redirect:/main.html?token="+uuid;
        }else {
            model.addAttribute("msg","账号密码错误");
            //回到登录页面
            return "login";
        }

    }

    /**
     * 去main页面
     * @return
     */
    @GetMapping("/main.html")
    public String mainPage(HttpSession session,Model model){

        //是否登录。  拦截器，过滤器
        Object loginUser = session.getAttribute("dbLoginsqlUser");
       if(loginUser == null)
        {
            //回到登录页面
            model.addAttribute("msg","请重新登录");
            return "login";
        }
        var exresult = new ArrayList<LinkedHashMap<String, Object>>();
        var resultdata =  new LinkedHashMap<String, Object>();
        resultdata.put("查询结果","暂无数据");
        exresult.add(resultdata);
        model.addAttribute("exresult",exresult);
        var clus  =  baseService.superManagerSelect("SELECT TABLE_NAME,CONCAT(COLUMN_NAME,\"(\",DATA_TYPE,\")\") AS COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'qctcrm' and COLUMN_NAME not in (\"id\",\"create_id\",\"update_id\",\"create_time\",\"update_time\",\"version\") ORDER BY TABLE_NAME,COLUMN_NAME");
        var tables  = new LinkedHashMap<String, ArrayList<String>>();
        for (var clu : clus)
        {

            if (tables.containsKey(clu.get("TABLE_NAME")))
            {
               tables.get(clu.get("TABLE_NAME")).add(clu.get("COLUMN_NAME").toString());
            }
            else
            {
                var tableclus = new ArrayList<String>();
                tableclus.add(clu.get("COLUMN_NAME").toString());
                tables.put(clu.get("TABLE_NAME").toString(),tableclus);
            }
        };
        model.addAttribute("tables",tables);
        return "main";
    }

    /**
     * 去main页面
     * @return
     */
    @PostMapping("/getresult")
    @ResponseResult
    public List<LinkedHashMap<String, Object>> getresult(@RequestBody ExSqlDto exsql, HttpSession session, Model model){

        List<LinkedHashMap<String, Object>> exresult = new ArrayList<LinkedHashMap<String, Object>>();
        Object loginUser = session.getAttribute("dbLoginsqlUser");
        if(loginUser == null)
        {
            var resultdata =  new LinkedHashMap<String, Object>();
            resultdata.put("查询结果", "请重新登录");
            exresult.add(resultdata);
            return exresult;
        }

        try {

            if(!StringUtils.hasLength(exsql.getExsql()))
            {
                var resultdata =  new LinkedHashMap<String, Object>();
                resultdata.put("查询结果","请输入SQL语句");
                exresult.add(resultdata);
            }
            else
            {
                var lastsqlbyte = DESUtils.decryptMode(Base64Util.decodeString(exsql.getExsql()));
                if (lastsqlbyte==null)
                {
                    throw new Exception("Sql格式不正确");
                }
                String lastsql =  new String (lastsqlbyte);
                if (exsql.getLimit() && !(lastsql.contains(" limit ") || lastsql.contains(" LIMIT ") ))
                {
                    lastsql = lastsql + " limit 5";
                }
                exresult = baseService.superManagerSelect(lastsql);
            }
        }
        catch (Exception ex)
        {
            var resultdata =  new LinkedHashMap<String, Object>();
            resultdata.put("查询结果", ExceptionUtil.getRealMessage(ex));
            exresult.add(resultdata);
        }
        if (exresult.size()==0)
        {
            var resultdata =  new LinkedHashMap<String, Object>();
            resultdata.put("查询结果", "执行成功,无返回数据");
            exresult.add(resultdata);
        }
        return exresult;
    }
}
